package com.cn.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.cn.bean.SimpleList;
import com.cn.bean.SimpleMap;
import com.cn.dao.model.TableBean;
import com.cn.exception.MyException;
import com.cn.xc.utils.ReflectUtil;

/**
 * @author sushy
 * @date 2016-3-25
 */
public class JdbcDaoImpl implements JdbcDao, java.io.Serializable {
	private static final long serialVersionUID = 1542634323213L;
	
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	public JdbcDaoImpl() {
		super();
		// 查询Date类型字段 没有了时分秒 全为 00 的解决办法
		System.setProperty("oracle.jdbc.V8Compatible", "true");
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	private NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		if (jdbcTemplate == null || dataSource != jdbcTemplate.getDataSource()) {
			jdbcTemplate = new JdbcTemplate(dataSource);
			namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
		}

	}

	private JdbcOperations getJdbcOperations() {
		return this.getNamedParameterJdbcTemplate().getJdbcOperations();
	}

	/**
	 * 执行SQL
	 * 
	 * @param sql
	 * @param param
	 * @return
	 */
	public int execSql(String sql, SimpleMap param) {
		if (param == null) {
			return execSql(sql);
		}

		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			return getNamedParameterJdbcTemplate().update(sql, param);
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} catch (Throwable t) {
			sw.setError(t.getMessage());
			// 抛异常以便回滚事务
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 执行SQL
	 * 
	 * @param sql
	 * @param param
	 */
	public int execSql(String sql, SimpleList param) {
		if (param == null) {
			return execSql(sql);
		}

		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			return getJdbcOperations().update(sql, param.toArray());
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} catch (Throwable t) {
			sw.setError(t.getMessage());
			// 抛异常以便回滚事务
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 执行SQL
	 * 
	 * @param sql
	 * @return
	 */
	public int execSql(String sql) {
		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			return getJdbcOperations().update(sql);
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} catch (Throwable t) {
			sw.setError(t.getMessage());
			// 抛异常以便回滚事务
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 输入查询SQL进行查询
	 * 
	 * @param sql
	 * @param rch
	 */
	public void query(String sql, RowCallbackHandler rch) {
		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			getJdbcOperations().query(sql, rch);
		} catch (EmptyResultDataAccessException e) {
			return;// 没有记录
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 输入查询SQL和绑定变量进行查询
	 * 
	 * @param sql
	 * @param param
	 * @param rch
	 */
	public void query(String sql, SimpleMap param, RowCallbackHandler rch) {
		if (param == null) {
			query(sql, rch);
			return;
		}

		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			getNamedParameterJdbcTemplate().query(sql, param, rch);
		} catch (EmptyResultDataAccessException e) {
			return;// 没有记录
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 输入查询SQL和绑定变量进行查询
	 * 
	 * @param sql
	 * @param param
	 * @param rch
	 */
	public void query(String sql, SimpleList param, RowCallbackHandler rch) {
		if (param == null) {
			query(sql, rch);
			return;
		}

		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			getJdbcOperations().query(sql, param.toArray(), rch);
		} catch (EmptyResultDataAccessException e) {
			return;// 没有记录
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 输入查询SQL和绑定变量进行查询
	 * 
	 * @param sql
	 * @param param
	 *            (绑定参数按:别名定义)
	 * @return 记录集
	 */
	public RowSet query(String sql, SimpleMap param) {
		if (param == null) {
			return query(sql);
		}

		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			SqlRowSet rs = getNamedParameterJdbcTemplate().queryForRowSet(sql, param);
			return new RowSet(rs);
		} catch (EmptyResultDataAccessException e) {
			return emptyRowSet;// 没有记录
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 输入查询SQL和绑定变量进行查询
	 * 
	 * @param sql
	 * @param param
	 *            (绑定变量参数数量必须一致)
	 * @return 记录集
	 */
	public RowSet query(String sql, SimpleList param) {
		if (param == null) {
			return query(sql);
		}

		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			SqlRowSet rs = getJdbcOperations().queryForRowSet(sql, param.toArray());
			return new RowSet(rs);
		} catch (EmptyResultDataAccessException e) {
			return emptyRowSet;// 没有记录
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 查询sql
	 * 
	 * @param sql
	 * @return
	 */
	public RowSet query(String sql) {
		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			SqlRowSet rs = getJdbcOperations().queryForRowSet(sql);
			return new RowSet(rs);
		} catch (EmptyResultDataAccessException e) {
			return emptyRowSet;// 没有记录
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} catch (Throwable t) {
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 批量取序列(至少会返回一个)
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Long> getSeqID(String seqName, int num) {
		SqlWatch sw = new SqlWatch();
		StringBuilder sqlBuilder = new StringBuilder("SELECT ").append(seqName).append(".NEXTVAL FROM DUAL");
		if (num > 1) {
			sqlBuilder.append(" CONNECT BY ROWNUM<=").append(num);
		}
		String sql = sqlBuilder.toString();

		try {
			sw.start();
			return getJdbcOperations().queryForList(sql, Long.class);
		} catch (EmptyResultDataAccessException e) {
			return (List<Long>) emptyList;
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 查询序列
	 * 
	 * @return
	 */
	public long getSeqID(String seqName) {
		SqlWatch sw = new SqlWatch();
		String sql = new StringBuilder("SELECT ").append(seqName).append(".NEXTVAL FROM DUAL").toString();
		try {
			sw.start();
			return getJdbcOperations().queryForObject(sql, Long.class);
		} catch (EmptyResultDataAccessException e) {
			return 0;
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 查询系统时间
	 * 
	 * @return
	 */
	public Date getSysDate() {
		RowSet rs = query(sysDateSql);
		if (rs.next()) {
			return rs.getDate(1);
		}
		throw new MyException("", "errMsg", "获取数据库时间失败");
	}

	/**
	 * 单表新增操作
	 * 
	 * @param objBean
	 * @return
	 */
	public int insert(TableBean objBean) {
		String sql = objBean.getParamMapper().getInsertSql();
		if (objBean.getParamMapper().isBranchTable()) {
			sql = sql.replaceAll(tableSuffix, objBean.getTableSuffix());
		}
		return execSql(sql, SimpleMap.toMap(objBean));
	}

	/**
	 * 单表更新操作
	 * 
	 * @param objBean
	 * @return
	 */
	public int update(TableBean objBean) {
		String sql = new StringBuilder(objBean.getParamMapper().getUpdateSql()).append(
				objBean.getParamMapper().getKeySql()).toString();
		if (objBean.getParamMapper().isBranchTable()) {
			sql = sql.replaceAll(tableSuffix, objBean.getTableSuffix());
		}
		return execSql(sql, SimpleMap.toMap(objBean));
	}

	/**
	 * 单表更新操作
	 * 
	 * @param objBean
	 * @return
	 */
	public int update(BaseMapper<?> mapper, SqlParam sqlParam) {
		String sql = new StringBuilder(mapper.getUpdateSql()).append(sqlParam.getParamSql()).toString();
		if (mapper.isBranchTable()) {
			sql = sql.replaceAll(tableSuffix, sqlParam.getTableSuffix());
		}
		return execSql(sql, sqlParam.getParam());
	}

	/**
	 * 单表删除操作
	 * 
	 * @param objBean
	 * @return
	 */
	public int delete(TableBean objBean) {
		String sql = new StringBuilder(objBean.getParamMapper().getDeleteSql()).append(
				objBean.getParamMapper().getKeySql()).toString();
		if (objBean.getParamMapper().isBranchTable()) {
			sql = sql.replaceAll(tableSuffix, objBean.getTableSuffix());
		}
		return execSql(sql, SimpleMap.toMap(objBean));
	}

	/**
	 * 单表删除操作
	 * 
	 * @param objBean
	 * @return
	 */
	public int delete(BaseMapper<?> mapper, SqlParam sqlParam) {
		String sql = new StringBuilder(mapper.getDeleteSql()).append(sqlParam.getParamSql()).toString();
		if (mapper.isBranchTable()) {
			sql = sql.replaceAll(tableSuffix, sqlParam.getTableSuffix());
		}
		return execSql(sql, sqlParam.getParam());
	}

	/**
	 * 拼接SQL查询操作
	 * 
	 * @param sqlParam
	 * @return
	 */
	public RowSet query(SqlParam sqlParam) {
		return query(sqlParam.getParamSql(), sqlParam.getParam());
	}

	/**
	 * 拼接SQL查询操作
	 * 
	 * @param sqlParam
	 * @return
	 */
	public void query(SqlParam sqlParam, RowCallbackHandler rch) {
		query(sqlParam.getParamSql(), sqlParam.getParam(), rch);
	}

	/**
	 * 拼接SQL查询操作
	 * 
	 * @param sqlParam
	 * @return
	 */
	public List<?> queryForList(SqlParam sqlParam) {
		String sql = sqlParam.getParamSql();
		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			SimpleMap param = sqlParam.getParam();
			if (param == null) {
				return getJdbcOperations().queryForList(sql);
			}
			return getNamedParameterJdbcTemplate().queryForList(sql, param);
		} catch (EmptyResultDataAccessException e) {
			return emptyList;// 没有记录
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 单表查询操作
	 * 
	 * @param mapper
	 * @param sqlParam
	 * @return
	 */
	public RowSet query(BaseMapper<?> mapper, SqlParam sqlParam) {
		String sql = new StringBuilder(mapper.getSelectSql()).append(sqlParam.getParamSql()).toString();
		if (mapper.isBranchTable()) {
			sql = sql.replaceAll(tableSuffix, sqlParam.getTableSuffix());
		}
		return query(sql, sqlParam.getParam());
	}

	/**
	 * 单表查询操作
	 * 
	 * @param <T>
	 * 
	 * @param mapper
	 * @param sqlParam
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T extends TableBean> T selectFirstRow(BaseMapper<T> mapper, SqlParam sqlParam) {
		String sql = new StringBuilder(mapper.getSelectSql()).append(sqlParam.getParamSql()).toString();
		if (mapper.isBranchTable()) {
			sql = sql.replaceAll(tableSuffix, sqlParam.getTableSuffix());
		}

		RowSet rs = query(sql, sqlParam.getParam());
		if (rs.next()) {
			String clsName = mapper.getClass().getName();
			int len = clsName.length();
			clsName = clsName.substring(0, len - 6) + "Bean";
			T bean = (T) ReflectUtil.newInstance(clsName);
			mapper.loadNext(rs, bean);
			return bean;
		}
		return null;
	}

	/**
	 * 单表查询操作
	 * 
	 * @param <T>
	 * 
	 * @param mapper
	 * @param sqlParam
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T extends TableBean> List<T> select(BaseMapper<T> mapper, SqlParam sqlParam) {
		String sql = new StringBuilder(mapper.getSelectSql()).append(sqlParam.getParamSql()).toString();
		if (mapper.isBranchTable()) {
			sql = sql.replaceAll(tableSuffix, sqlParam.getTableSuffix());
		}

		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			return getNamedParameterJdbcTemplate().query(sql, sqlParam.getParam(), mapper);
		} catch (EmptyResultDataAccessException e) {
			return (List<T>) emptyList;// 没有记录
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}


	/**
	 * 批量新增
	 * 
	 * @param beans
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T extends TableBean> void batchInsert(BaseMapper<T> mapper, List<T> beans) {
		String sql = mapper.getInsertSql();

		if (mapper.isBranchTable()) {
			// 分表
			String suffix = null;
			int i = 0;
			String[] sqls = new String[10];
			List<T>[] values = new ArrayList[10];
			for (T objBean : beans) {
				suffix = objBean.getTableSuffix();
				i = Integer.valueOf(suffix);
				if (i < 0 || i >= 10) {
					continue;
				}

				if (sqls[i] == null) {
					sqls[i] = sql.replaceAll(tableSuffix, suffix);
					values[i] = new ArrayList();
				}
				values[i].add(objBean);
				++i;
			}

			for (i = 0; i < sqls.length; ++i) {
				if (sqls[i] != null) {
					batchUpdate(sqls[i], values[i]);
				}
			}
		} else {
			batchUpdate(sql, beans);
		}
	}

	/**
	 * 批量更新
	 * 
	 * @param beans
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T extends TableBean> void batchUpdate(BaseMapper<T> mapper, List<T> beans) {
		String sql = new StringBuilder(mapper.getUpdateSql()).append(mapper.getKeySql()).toString();

		if (mapper.isBranchTable()) {
			// 分表
			String suffix = null;
			int i = 0;
			String[] sqls = new String[10];
			List<T>[] values = new ArrayList[10];
			for (T objBean : beans) {
				suffix = objBean.getTableSuffix();
				i = Integer.valueOf(suffix);
				if (i < 0 || i >= 10) {
					continue;
				}

				if (sqls[i] == null) {
					sqls[i] = sql.replaceAll(tableSuffix, suffix);
					values[i] = new ArrayList();
				}
				values[i].add(objBean);
				++i;
			}

			for (i = 0; i < sqls.length; ++i) {
				if (sqls[i] != null) {
					batchUpdate(sqls[i], values[i]);
				}
			}
		} else {
			batchUpdate(sql, beans);
		}
	}

	/**
	 * 批量删除
	 * 
	 * @param beans
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T extends TableBean> void batchDelete(BaseMapper<T> mapper, List<T> beans) {
		String sql = new StringBuilder(mapper.getDeleteSql()).append(mapper.getKeySql()).toString();
		if (mapper.isBranchTable()) {
			// 分表
			String suffix = null;
			int i = 0;
			String[] sqls = new String[10];
			List<T>[] values = new ArrayList[10];
			for (T objBean : beans) {
				suffix = objBean.getTableSuffix();
				i = Integer.valueOf(suffix);
				if (i < 0 || i >= 10) {
					continue;
				}

				if (sqls[i] == null) {
					sqls[i] = sql.replaceAll(tableSuffix, suffix);
					values[i] = new ArrayList();
				}
				values[i].add(objBean);
				++i;
			}

			for (i = 0; i < sqls.length; ++i) {
				if (sqls[i] != null) {
					batchUpdate(sqls[i], values[i]);
				}
			}
		} else {
			batchUpdate(sql, beans);
		}
	}

	/**
	 * 批量更新(适用单SQL语句批量操作)
	 * 
	 * @param values
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void batchUpdate(String sql, List<?> values) {
		int length = values.size();
		if (values == null || values.size() <= 0) {
			return;
		}

		SqlParameterSource batchArgs[] = new SqlParameterSource[length];

		int i = 0;
		for (Object param : values) {
			if (param instanceof Map) {
				batchArgs[i] = new MapSqlParameterSource((Map) param);
			} else if (param instanceof TableBean) {
				batchArgs[i] = new MapSqlParameterSource(SimpleMap.toMap(param));
			} else {
				batchArgs[i] = new BeanPropertySqlParameterSource(param);
			}
			++i;
		}

		SqlWatch sw = new SqlWatch();
		try {
			sw.start();
			getNamedParameterJdbcTemplate().batchUpdate(sql, batchArgs);
		} catch (DataAccessException e) {
			Throwable t = e.getCause();
			if (t == null) {
				t = e;
			}
			sw.setError(t.getMessage());
			throw new MyException("", "errMsg", t.getMessage());
		} catch (Throwable t) {
			sw.setError(t.getMessage());
			// 抛异常以便回滚事务
			throw new MyException("", "errMsg", t.getMessage());
		} finally {
			sw.stop();
		}
	}

	/**
	 * 查询返回一行0..1列
	 * 
	 * @param sqlParam
	 * @return
	 */
	public String queryForString(SqlParam sqlParam) {
		RowSet rs = query(sqlParam);
		if (rs.next()) {
			return rs.getString(1);
		}
		return null;
	}

	/**
	 * 查询返回一行0..1列
	 * 
	 * @param sqlParam
	 * @return
	 */
	public long queryForLong(SqlParam sqlParam) {
		RowSet rs = query(sqlParam);
		if (rs.next()) {
			return rs.getLong(1);
		}
		return 0;
	}

	@Override
	public void callProcedure(String callString) {
		jdbcTemplate.execute(callString);
	}

	@Override
	public Object callProcedure(String callString, CallableStatementCallback<?> action) {
		return jdbcTemplate.execute(callString, action);
	}

}
